package com.cup.bddm.impala.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.cup.bddm.impala.Constant;
import com.cup.bddm.impala.ImpalaJdbc;
import com.cup.bddm.impala.po.MchntDailyTrans;
import com.cup.bddm.impala.po.StreetAppDailyTrans;
import com.cup.bddm.impala.po.StreetDailyTrans;
import com.cup.bddm.impala.po.StreetInfo;
import com.cup.bddm.impala.po.StreetMchntInfo;
import com.cup.bddm.impala.po.StreetMonthData;
import com.cup.bddm.util.DateUtil;

@Repository("streetDao")
public class StreetDao {

	static Logger logger = LoggerFactory.getLogger(StreetDao.class);

	@Autowired
	private ImpalaJdbc impalaJdbc;

	public StreetInfo getStreetInfo(String streetCd) {
		String row_key_start = streetCd + "_";
		String row_key_end = streetCd + "}";
		String sql = "SELECT biz_street_cd,biz_street_nm,cup_branch_ins_id_cd, cup_branch_nm, service_mchnt_cd, service_mchnt_nm,city " + "FROM " + Constant.TBL_QRC_STREET_INFO + " where row_key>=? and row_key<=?";

		logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + streetCd);

		StreetInfo streetInfo = null;
		try {
			Map<String, Object> streetMap = impalaJdbc.queryForMap(sql, row_key_start, row_key_end);

			if (streetMap != null) {
				streetInfo = new StreetInfo();

				streetInfo.setStreet_cd(streetMap.get("biz_street_cd").toString());
				streetInfo.setStreet_nm(streetMap.get("biz_street_nm").toString());
				streetInfo.setCup_branch_ins_id_cd(streetMap.get("cup_branch_ins_id_cd").toString());
				streetInfo.setCup_branch_nm(streetMap.get("cup_branch_nm").toString());
				streetInfo.setService_mchnt_cd(streetMap.get("service_mchnt_cd").toString());
				streetInfo.setService_mchnt_nm(streetMap.get("service_mchnt_nm").toString());
				streetInfo.setCity(streetMap.get("city").toString());

			}

		} catch (SQLException e) {
			logger.error("error in query street info", e);
			throw new RuntimeException("error in query street info");
		}

		return streetInfo;
	}

	public StreetDailyTrans getStreetDailyTrans(String streetCd, String date, String type) {
		String sql = "";
		if (type.equals("all")) {
			sql = "select * from " + Constant.TBL_STREET_ALL_DAY_TRANS + " where row_key=?";
		} else if (type.equals("nfc")) {
			sql = "select * from " + Constant.TBL_STREET_NFC_DAY_TRANS + " where row_key=?";
		} else if (type.equals("qrc")) {
			sql = "select * from " + Constant.TBL_STREET_QRC_DAY_TRANS + " where row_key=?";
		} else if (type.equals("offline")) {
			sql = "select * from " + Constant.TBL_STREET_OFFLINE_DAY_TRANS + " where row_key=?";
		} else {
			return null;
		}

		logger.info("exacute sql:" + sql + " . paremeter: biz_street_cd=" + streetCd + ". pdate=" + date);
		StreetDailyTrans dailyTrans = new StreetDailyTrans();

		try {
			Map<String, Object> map = impalaJdbc.queryForMap(sql, streetCd + "|" + date);
			if (map != null) {

				dailyTrans.setPdate(map.get("pdate").toString());
				dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));
				dailyTrans.setTrans_sum((BigDecimal) map.get("trans_sum"));
				dailyTrans.setAvg_trans_at((BigDecimal) map.get("avg_trans_at"));
				dailyTrans.setCard_count((BigDecimal) map.get("card_count"));
				dailyTrans.setLe_10_count((BigDecimal) map.get("le_10_count"));
				dailyTrans.setBw_10_50_count((BigDecimal) map.get("bw_10_50_count"));
				dailyTrans.setBw_50_200_count((BigDecimal) map.get("bw_50_200_count"));
				dailyTrans.setBw_200_1000_count((BigDecimal) map.get("bw_200_1000_count"));
				dailyTrans.setGt_1000_count((BigDecimal) map.get("gt_1000_count"));
			} else {

				dailyTrans.setPdate(date);
				dailyTrans.setTrans_count(new BigDecimal("0"));
				dailyTrans.setTrans_sum(new BigDecimal("0"));
				dailyTrans.setAvg_trans_at(new BigDecimal("0"));
				dailyTrans.setCard_count(new BigDecimal("0"));
				dailyTrans.setLe_10_count(new BigDecimal("0"));
				dailyTrans.setBw_10_50_count(new BigDecimal("0"));
				dailyTrans.setBw_50_200_count(new BigDecimal("0"));
				dailyTrans.setBw_200_1000_count(new BigDecimal("0"));
				dailyTrans.setGt_1000_count(new BigDecimal("0"));
			}
		} catch (SQLException e) {
			logger.error("error in query street day trans ", e);
			throw new RuntimeException("error in query street  day trans info");
		}
		return dailyTrans;
	}

	public Map<String, StreetDailyTrans> getStreetTransByTransType(String streetCd, String date) {
		Map<String, StreetDailyTrans> transTypeDis = new HashMap<String, StreetDailyTrans>();

		StreetDailyTrans nfcTrans = getStreetDailyTrans(streetCd, date, "nfc");
		StreetDailyTrans qrcTrans = getStreetDailyTrans(streetCd, date, "qrc");
		StreetDailyTrans offlineTrans = getStreetDailyTrans(streetCd, date, "offline");

		transTypeDis.put("nfc", nfcTrans);
		transTypeDis.put("qrc", qrcTrans);
		transTypeDis.put("offline", offlineTrans);

		return transTypeDis;
	}

	public Map<String, StreetDailyTrans> getStreetTransByMchntType(String streetCd, String date) {
		String nextDay = DateUtil.getNextDay(date);
		String row_key_start = streetCd + "|" + date;
		String row_key_end = streetCd + "|" + nextDay;
		String sql = "select mchnt_tp, trans_count from " + Constant.TBL_STREET_MCHNT_TP_DAY_TRANS + " where row_key>=? and row_key<?";

		logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ". row_key_end=" + row_key_end);
		Map<String, StreetDailyTrans> mchntTpTrans = new HashMap<String, StreetDailyTrans>();

		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, row_key_start, row_key_end);
			for (Map<String, Object> map : list) {
				StreetDailyTrans dayTrans = new StreetDailyTrans();

				dayTrans.setTrans_count((BigDecimal) map.get("trans_count"));
				mchntTpTrans.put(map.get("mchnt_tp").toString(), dayTrans);
			}

		} catch (SQLException e) {
			logger.error("error in query street trans dist by mchnt tp ", e);
			throw new RuntimeException("error in query street day trans dist by mchnt tp");
		}
		return mchntTpTrans;
	}

	public List<MchntDailyTrans> getTop5Mchnt(String streetCd, String type, String date) {
		String nextDay = DateUtil.getNextDay(date);
		String row_key_start = streetCd + "|" + date;
		String row_key_end = streetCd + "|" + nextDay;
		// 已修改，将top5 改成所有商户
		String sql = "";
		if (type.equals("nfc")) {
			sql = "select mchnt_cd,mchnt_nm,trans_count, trans_sum  from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " where row_key>=? and row_key<?" + " ORDER BY trans_count desc";

		} else if (type.equals("qrc")) {
			sql = "select mchnt_cd,mchnt_nm,trans_count, trans_sum  from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " where row_key>=? and row_key<?" + " ORDER BY trans_count desc";

		} else if (type.equals("offline")) {
			sql = "select mchnt_cd,mchnt_nm,trans_count, trans_sum  from " + Constant.TBL_MCHNT_OFFLINE_DAY_TRANS + " where row_key>=? and row_key<?" + " ORDER BY trans_count desc";
		}

		logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ",row_key_end=" + row_key_end);

		List<MchntDailyTrans> result = new ArrayList<MchntDailyTrans>();
		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, row_key_start, row_key_end);
			for (Map<String, Object> map : list) {
				MchntDailyTrans mchntTrans = new MchntDailyTrans();
				mchntTrans.setMchnt_cd(map.get("mchnt_cd").toString());
				mchntTrans.setMchnt_nm(map.get("mchnt_nm").toString());
				mchntTrans.setTrans_count((BigDecimal) map.get("trans_count"));
				mchntTrans.setTrans_sum((BigDecimal) map.get("trans_sum"));
				result.add(mchntTrans);
			}
		} catch (SQLException e) {
			logger.error("error in query top merchant in street ", e);
			throw new RuntimeException("error in query top merchant in street ");
		}
		return result;
	}

	public List<StreetMonthData> getRecentMonthsTrans(String streetCd, String month) {

		String sql = "select month,trans_count from " + Constant.TBL_STREET_ALL_MONTH_TRANS + " where row_key<? and row_key>=? order by month";

		String last6Month = DateUtil.getPreNMonth(month, 6);

		logger.info("exacute sql:" + sql + " . paremeter: biz_street_cd=" + streetCd + ",month=" + month);

		List<StreetMonthData> result = new ArrayList<StreetMonthData>();
		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, streetCd + "|" + month, streetCd + "|" + last6Month);
			for (Map<String, Object> map : list) {
				StreetMonthData streetMonthTrans = new StreetMonthData();
				streetMonthTrans.setMonth(map.get("month").toString());
				streetMonthTrans.setCount((BigDecimal) map.get("trans_count"));

				result.add(streetMonthTrans);
			}
		} catch (SQLException e) {
			logger.error("error in query recent street month trans ", e);
			throw new RuntimeException("error in query recent street month trans ");
		}

		return result;
	}

	public Map<String, List<StreetMonthData>> getRecentMonthsTpTrans(String streetCd, String month) {
		String last6Month = DateUtil.getPreNMonth(month, 6);
		String row_key_start = streetCd + "|" + last6Month;
		String row_key_end = streetCd + "|" + month;
		String sql = "select month,mchnt_tp,trans_count from " + Constant.TBL_STREET_MCHNT_TP_MONTH_TRANS + " where row_key>=? AND row_key<?" + "  order by month";

		logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ",row_key_end=" + row_key_end);

		Map<String, List<StreetMonthData>> result = new HashMap<String, List<StreetMonthData>>();

		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, row_key_start, row_key_end);
			for (Map<String, Object> map : list) {
				StreetMonthData streetMonthTrans = new StreetMonthData();
				streetMonthTrans.setMonth(map.get("month").toString());
				streetMonthTrans.setCount((BigDecimal) map.get("trans_count"));
				String mchntTp = map.get("mchnt_tp").toString();

				if (result.get(mchntTp) != null) {
					List<StreetMonthData> monthList = result.get(mchntTp);
					monthList.add(streetMonthTrans);
					result.put(mchntTp, monthList);
				} else {
					List<StreetMonthData> monthList = new ArrayList<StreetMonthData>();
					monthList.add(streetMonthTrans);
					result.put(mchntTp, monthList);
				}

			}
		} catch (SQLException e) {
			logger.error("error in query recent street month mchnttp trans ", e);
			throw new RuntimeException("error in query recent street month mchnttp trans ");
		}

		return result;
	}

	public List<StreetMonthData> getRecentMonthsActiveMchntNum(String streetCd, String month) {
		String sql = "select month, active_mchnt_count from " + Constant.TBL_STREET_ALL_MONTH_TRANS + " where row_key<? and row_key>=?";

		logger.info("exacute sql:" + sql + " . paremeter: biz_street_cd=" + streetCd + ",month=" + month);

		String last6Month = DateUtil.getPreNMonth(month, 6);
		List<StreetMonthData> result = new ArrayList<StreetMonthData>();
		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, streetCd + "|" + month, streetCd + "|" + last6Month);
			for (Map<String, Object> map : list) {
				StreetMonthData streetMonthTrans = new StreetMonthData();
				streetMonthTrans.setMonth(map.get("month").toString());
				streetMonthTrans.setCount((BigDecimal) map.get("active_mchnt_count"));

				result.add(streetMonthTrans);
			}
		} catch (SQLException e) {
			logger.error("error in query recent street month active mchnt ", e);
			throw new RuntimeException("error in query recent street month  active mchnt ");
		}

		return result;
	}

	public Map<String, Integer> getLiveMchntNum(String streetCd, String month) {
		String nextMonth = DateUtil.getPreNMonth(month, -1);
		String row_key_start = streetCd + "|" + month;
		String row_key_end = streetCd + "|" + nextMonth;
		String sql = "select pdate,count(distinct mchnt_cd) as count from " + Constant.TBL_MCHNT_ALL_DAY_TRANS + " where row_key>=? and row_key <? " + " and trans_count>0 group by pdate order by pdate";

		logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ",row_key_end=" + row_key_end);

		Map<String, Integer> result = new HashMap<String, Integer>();
		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, row_key_start, row_key_end);
			for (Map<String, Object> map : list) {

				String date = map.get("pdate").toString();
				Long longCount = (Long) map.get("count");

				Integer count = longCount.intValue();

				result.put(date, count);
			}
		} catch (SQLException e) {
			logger.error("error in query recent street month live mchnt num ", e);
			throw new RuntimeException("error in query recent street month  live mchnt num ");
		}

		return result;
	}

	public List<StreetMchntInfo> getTopFallMchnt(String streetCd, String date, String type) {
		String curWeekStart = DateUtil.getPreNday(date, 6);
		String lastWeekStart = DateUtil.getPreNday(date, 13);
		String lastWeekEnd = DateUtil.getPreNday(date, 7);
		String row_key_start = streetCd + "|" + lastWeekStart;
		String row_key_end = streetCd + "|" + date;
		String sql = "";
		if (type.equals("nfc")) {
			sql = "select t.mchnt_cd,t.mchnt_nm, t.pre_count as pre_count,t.cur_count as cur_count,(t.pre_count-t.cur_count)*1.0/t.pre_count as decl" + " from (SELECT mchnt_cd,mchnt_nm,sum(if(pdate>=? and pdate<=?,trans_count,0)) as pre_count,sum(if(pdate>=? and pdate<=?,trans_count,0)) as cur_count from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " where row_key BETWEEN ? and ? GROUP BY mchnt_cd,mchnt_nm)t where t.pre_count>t.cur_count order by decl desc limit 10";
		} else if (type.equals("qrc")) {
			sql = "select t.mchnt_cd,t.mchnt_nm, t.pre_count as pre_count,t.cur_count as cur_count,(t.pre_count-t.cur_count)*1.0/t.pre_count as decl" + " from (SELECT mchnt_cd,mchnt_nm,sum(if(pdate>=? and pdate<=?,trans_count,0)) as pre_count,sum(if(pdate>=? and pdate<=?,trans_count,0)) as cur_count from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " where row_key BETWEEN ? and ? GROUP BY mchnt_cd,mchnt_nm)t where t.pre_count>t.cur_count order by decl desc limit 10";
		} else {
			return null;
		}

		logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ",row_key_end=" + row_key_end);
		List<StreetMchntInfo> result = new ArrayList<StreetMchntInfo>();

		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, lastWeekStart, lastWeekEnd, curWeekStart, date, row_key_start, row_key_end);
			for (Map<String, Object> map : list) {
				StreetMchntInfo mchntInfo = new StreetMchntInfo();
				mchntInfo.setMchnt_cd(map.get("mchnt_cd").toString());
				mchntInfo.setMchnt_nm(map.get("mchnt_nm").toString());
				mchntInfo.setPre_trans_count((BigDecimal.valueOf((Long) map.get("pre_count"))));
				mchntInfo.setTrans_count((BigDecimal.valueOf((Long) map.get("cur_count"))));

				result.add(mchntInfo);
			}
		} catch (SQLException e) {
			logger.error("error in query street month trans count Less Than 5 Mchnt ", e);
			throw new RuntimeException("error in query street month  trans count Less Than 5 Mchnt");
		}

		return result;

	}

	public List<StreetMchntInfo> getLessThan5Mchnt(String streetCd, String date, String type) {
		String month = date.substring(0, 6);
		String row_key_start = streetCd + "|" + month;
		String row_key_end = streetCd + "|" + date;
		String sql = "";
		if (type.equals("nfc")) {
			sql = "select t.mchnt_cd,t.mchnt_nm,t.count from " + "(select mchnt_cd, mchnt_nm, sum(trans_count) as count from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " where row_key>=? and row_key<=?" + " group by mchnt_cd, mchnt_nm)t where t.count<5";
		} else if (type.equals("qrc")) {
			sql = "select t.mchnt_cd,t.mchnt_nm,t.count from " + "(select mchnt_cd, mchnt_nm, sum(trans_count) as count from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " where row_key>=? and row_key<=?" + " group by mchnt_cd, mchnt_nm)t where t.count<5";
		} else {
			return null;
		}

		logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ",row_key_end=" + row_key_end);
		List<StreetMchntInfo> result = new ArrayList<StreetMchntInfo>();
		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, row_key_start, row_key_end);
			for (Map<String, Object> map : list) {
				StreetMchntInfo mchntInfo = new StreetMchntInfo();
				mchntInfo.setMchnt_cd(map.get("mchnt_cd").toString());
				mchntInfo.setMchnt_nm(map.get("mchnt_nm").toString());
				mchntInfo.setTrans_count((BigDecimal) map.get("count"));
				result.add(mchntInfo);
			}
		} catch (SQLException e) {
			logger.error("error in query street month trans count Less Than 5 Mchnt ", e);
			throw new RuntimeException("error in query street month  trans count Less Than 5 Mchnt");
		}

		return result;
	}

	public List<StreetAppDailyTrans> getSortedAppTrans(String streetCd, String date) {
		String sql = "SELECT app_ins_id_cd,app_ins_id_nm, sum(trans_count) as trans_count from " + Constant.TBL_STREET_APP_DAY_TRANS + " where row_key>=? and row_key<? GROUP BY app_ins_id_cd,app_ins_id_nm ORDER BY trans_count desc";

		logger.info("exacute sql:" + sql + " . paremeter: biz_street_cd=" + streetCd + ",date=" + date);

		List<StreetAppDailyTrans> result = new ArrayList<StreetAppDailyTrans>();
		try {
			List<Map<String, Object>> list = impalaJdbc.queryForList(sql, streetCd + "|" + date.substring(0, 6), streetCd + "|" + DateUtil.getNextDay(date));
			for (Map<String, Object> map : list) {
				StreetAppDailyTrans appDailyTrans = new StreetAppDailyTrans();
				appDailyTrans.setApp_ins_id_cd(map.get("app_ins_id_cd").toString());
				appDailyTrans.setApp_ins_id_nm(map.get("app_ins_id_nm").toString());
				appDailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));
				result.add(appDailyTrans);
			}
		} catch (SQLException e) {
			logger.error("error in query street month qrc app trans sorted list", e);
			throw new RuntimeException("error in query street month  qrc app trans sorted list");
		}

		return result;
	}

	public List<StreetInfo> updateStreetInfo(List<StreetInfo> streets) {

		String sql = "insert into " + Constant.TBL_QRC_STREET_INFO + "(row_key,biz_street_cd,biz_street_nm,biz_street_addr,city,cup_branch_ins_id_cd,cup_branch_nm,service_mchnt_cd,service_mchnt_nm,man_hour,mchnt_num,rec_upd_ts,rec_crt_ts) values(?,?,?,?,?,?,?,?,?,?,?,now(),now());";

		List<StreetInfo> result = new ArrayList<StreetInfo>();
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = impalaJdbc.getConnection();
			stmt = conn.prepareStatement(sql);

			for (StreetInfo street : streets) {
				try {
					stmt.setString(1, street.getStreet_cd() + "_" + street.getService_mchnt_cd());
					stmt.setString(2, street.getStreet_cd());
					stmt.setString(3, street.getStreet_nm());
					stmt.setString(4, street.getStreet_addr());
					stmt.setString(5, street.getCity());
					stmt.setString(6, street.getCup_branch_ins_id_cd());
					stmt.setString(7, street.getCup_branch_nm());
					stmt.setString(8, street.getService_mchnt_cd());
					stmt.setString(9, street.getService_mchnt_nm());
					stmt.setInt(10, Integer.valueOf(StringUtils.isEmpty(street.getMan_hour()) ? "0" : street.getMan_hour()));
					stmt.setInt(11, Integer.valueOf(StringUtils.isEmpty(street.getMchnt_num()) ? "0" : street.getMchnt_num()));

					logger.info("excute sql=" + sql);
					logger.info("row_key=" + street.getStreet_cd() + "_" + street.getService_mchnt_cd() + "biz_street_cd=" + street.getStreet_cd() + "biz_street_nm=" + street.getStreet_nm() + "biz_street_addr=" + street.getStreet_addr() + "city=" + street.getCity() + "cup_branch_ins_id_cd=" + street.getCup_branch_ins_id_cd() + "cup_branch_nm=" + street.getCup_branch_nm() + "service_mchnt_cd=" + street.getService_mchnt_cd() + "service_mchnt_nm=" + street.getService_mchnt_nm() + "mchnt_num" + street.getMchnt_num());

					stmt.execute();
				} catch (Exception e) {
					logger.error("error in insert street info", e);
					result.add(street);
				}

			}
		} catch (SQLException e) {
			logger.error("error in insert street info", e);
			throw new RuntimeException("error in insert street info");
		} finally {
			impalaJdbc.close(stmt);
			impalaJdbc.close(conn);
		}

		return result;

	}

	public List<StreetInfo> updateStreetInfo_m(List<StreetInfo> streets) {

		String sql = "insert into " + Constant.TBL_QRC_STREET_INFO_TMP + "(row_key,biz_street_cd,biz_street_nm,biz_street_addr,city,cup_branch_ins_id_cd,cup_branch_nm,service_mchnt_cd,service_mchnt_nm,man_hour,mchnt_num,rec_upd_ts,rec_crt_ts) values(?,?,?,?,?,?,?,?,?,?,?,now(),now());";

		List<StreetInfo> result = new ArrayList<StreetInfo>();
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = impalaJdbc.getConnection();
			stmt = conn.prepareStatement(sql);

			for (StreetInfo street : streets) {
				try {
					stmt.setString(1, street.getStreet_cd() + "_" + street.getService_mchnt_cd());
					stmt.setString(2, street.getStreet_cd());
					stmt.setString(3, street.getStreet_nm());
					stmt.setString(4, street.getStreet_addr());
					stmt.setString(5, street.getCity());
					stmt.setString(6, street.getCup_branch_ins_id_cd());
					stmt.setString(7, street.getCup_branch_nm());
					stmt.setString(8, street.getService_mchnt_cd());
					stmt.setString(9, street.getService_mchnt_nm());
					stmt.setInt(10, Integer.valueOf(StringUtils.isEmpty(street.getMan_hour()) ? "0" : street.getMan_hour()));
					stmt.setInt(11, Integer.valueOf(StringUtils.isEmpty(street.getMchnt_num()) ? "0" : street.getMchnt_num()));

					logger.info("excute sql=" + sql);
					logger.info("row_key=" + street.getStreet_cd() + "_" + street.getService_mchnt_cd() + "biz_street_cd=" + street.getStreet_cd() + "biz_street_nm=" + street.getStreet_nm() + "biz_street_addr=" + street.getStreet_addr() + "city=" + street.getCity() + "cup_branch_ins_id_cd=" + street.getCup_branch_ins_id_cd() + "cup_branch_nm=" + street.getCup_branch_nm() + "service_mchnt_cd=" + street.getService_mchnt_cd() + "service_mchnt_nm=" + street.getService_mchnt_nm() + "mchnt_num" + street.getMchnt_num());

					stmt.execute();
				} catch (Exception e) {
					logger.error("error in insert street info", e);
					result.add(street);
				}

			}
		} catch (SQLException e) {
			logger.error("error in insert street info", e);
			throw new RuntimeException("error in insert street info");
		} finally {
			impalaJdbc.close(stmt);
			impalaJdbc.close(conn);
		}

		return result;

	}

	/**
	 * biz_street_cd必须是前五位数，eg：2900S，2900J，2900D
	 */
	public String getMaxStreetCd(String biz_street_cd) {
		String sql = "SELECT max(strright(biz_street_cd,4)) as biz_street_cd FROM tbl_qrc_biz_street_info_v2 where biz_street_cd like  concat(?,'%');";

		logger.info("exacute sql:" + sql + " . paremeter: biz_street_cd=" + biz_street_cd);
		String maxStreetCd = null;
		try {
			Map<String, Object> streetMap = impalaJdbc.queryForMap(sql, biz_street_cd);
			if (streetMap != null) {
				Object obj = streetMap.get("biz_street_cd");
				if (obj != null) {
					maxStreetCd = obj.toString();
				}
			}
		} catch (SQLException e) {
			logger.error("error in query street maxstreetcd", e);
			throw new RuntimeException("error in query street maxstreetcd");
		}
		return maxStreetCd;
	}
}
